﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Pixels.Core;

namespace Pixels.DataLayer
{
    internal class SqlQueryBuilder
    {
        public static string GetUpdateQuery(object objInstance, string primarykey)
        {
            string TableName = objInstance.GetType().Name;
            string PrimaryKey = primarykey;
            string columns = "";
            string wherecolumns = "";

            foreach (var item in objInstance.GetType().GetProperties())
            {
                if (!item.HasCustomAttribute("PixelNavigation"))
                {
                    if (item.Name.ToLowerInvariant() != "createddate")
                    {
                        if (item.Name.ToLowerInvariant() != "modifieddate")
                        {
                            object val = item.GetValue(objInstance, null);
                            if (PrimaryKey != item.Name)
                            {
                                val = GetTypeDefault(val);
                                if (val.ToString() != "False")
                                    columns += item.Name + " = " + val.ToString() + ", ";
                            }
                            else
                                wherecolumns = item.Name + " = '" + val.ToString() + "'";
                        }
                        else
                        {                            
                            columns += item.Name + " = '" + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt") + "', ";
                        }
                    }
                }
            }

            columns = columns.Trim().TrimEnd(',');

            return "Update " + TableName + " SET " + columns + " WHERE " + wherecolumns;
        }

        public static string GetInsertQuery(object objInstance)
        {
            string TableName = objInstance.GetType().Name;
            string columns = "";
            string values = "";
            object val = null;
            bool hasdate = false;
            foreach (var item in objInstance.GetType().GetProperties())
            {
                if (!item.HasCustomAttribute("PixelNavigation"))
                {
                    if (item.Name.ToLowerInvariant() != "createddate" && item.Name.ToLowerInvariant() != "modifieddate")
                    {
                        val = item.GetValue(objInstance, null);
                        val = GetTypeDefault(val);
                        if (val.ToString() != "False")
                        {
                            columns += item.Name + ", ";
                            values += val.ToString().Trim() + ",";
                        }
                    }
                    else
                    {
                        if (hasdate != true)
                            hasdate = true;
                        else
                            hasdate = false;
                    }

                    if (hasdate == true)
                    {
                        columns += "CreatedDate,Modifieddate,";
                        values += "'" + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt") + "','" + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt") + "',";
                    }
                }
            }

            columns = columns.Trim().TrimEnd(',');
            values = values.Trim().TrimEnd(',');

            return "INSERT INTO " + TableName + " (" + columns + ") VALUES (" + values + ")";
        }

        public static object GetTypeDefault(object val)
        {
            if (val == null)
                return false;
            switch (val.GetType().Name.ToLowerInvariant())
            {
                case "string":
                    {
                        if (!string.IsNullOrEmpty(val.ToString().Trim()))
                            return "'" + val.ToString().Replace("'", "''") + "'";
                        else
                            return false;
                    }
                case "int":
                    {
                        if (int.Parse(val.ToString()) != 0)
                            return val;
                        else
                            return false;
                    }
                case "int32":
                    {
                        if (int.Parse(val.ToString()) != 0)
                            return val;
                        else
                            return false;
                    }
                case "guid":
                    {
                        if (val.ToString() != "00000000-0000-0000-0000-000000000000")
                            return "'" + val + "'";
                        else
                            return false;
                    }
                case "datetime":
                    {
                        if (val.ToString() != "")
                            return "'" + val + "'";
                        else
                            return false;
                    }
                default:
                    return "";
            }
        }
    }
}
